-- Adminer 3.7.1 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = '+01:00';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `Annotation`;
CREATE TABLE `Annotation` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `SubId` tinyint(4) NOT NULL,
  `Tag` varchar(255) NOT NULL,
  `Annotation` varchar(8000) NOT NULL,
  PRIMARY KEY (`FK_Document`,`Id`,`SubId`),
  CONSTRAINT `FK_Annotation_Document` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Chromatograms`;
CREATE TABLE `Chromatograms` (
  `id` char(38) NOT NULL,
  `name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `DataProvider`;
CREATE TABLE `DataProvider` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` tinyint(4) NOT NULL,
  `Uri` varchar(255) NOT NULL,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  CONSTRAINT `FK_DataProvider_Document` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `DevelopmentalStage`;
CREATE TABLE `DevelopmentalStage` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Observation` int(11) NOT NULL,
  `FK_Term` smallint(6) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Observation`,`FK_Term`),
  KEY `FK_DevelopmentalStage_Term` (`FK_Document`,`FK_Term`),
  CONSTRAINT `FK_DevelopmentalStage_Term` FOREIGN KEY (`FK_Document`, `FK_Term`) REFERENCES `Term` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_DevelopmentalStage_Observation` FOREIGN KEY (`FK_Document`, `FK_Observation`) REFERENCES `Observation` (`FK_Document`, `Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Document`;
CREATE TABLE `Document` (
  `Id` smallint(6) NOT NULL AUTO_INCREMENT,
  `XemlId` char(36) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `StartDate` datetime NOT NULL,
  `ObservationTime` float NOT NULL,
  `Description` varchar(8000) DEFAULT NULL,
  `ExperimenterEmail` varchar(255) DEFAULT NULL,
  `ExperimenterFirstName` varchar(255) DEFAULT NULL,
  `ExperimenterLastName` varchar(255) DEFAULT NULL,
  `Organisation` varchar(255) DEFAULT NULL,
  `NameSpace` varchar(255) NOT NULL,
  `XemlVersion` varchar(255) NOT NULL,
  `Xeml` longtext NOT NULL,
  `DATE` datetime NOT NULL,
  `USER_NAME` varchar(256) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `FK_Experiment_Experimenter` (`ExperimenterEmail`),
  CONSTRAINT `FK_Experiment_Experimenter` FOREIGN KEY (`ExperimenterEmail`) REFERENCES `Experimenter` (`Email`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER ;;

CREATE TRIGGER `xemlDocumentSetNow` BEFORE INSERT ON `Document` FOR EACH ROW
BEGIN
    if (new.DATE IS NULL OR new.DATE = '' OR new.DATE = '00-00-00 00:00:00') THEN
        set new.DATE = NOW();
    END IF;
    if (new.USER_NAME IS NULL OR new.USER_NAME = '') THEN
        set new.USER_NAME = USER();
    END IF;
END;;

DELIMITER ;

DROP TABLE IF EXISTS `Document_audit`;
CREATE TABLE `Document_audit` (
  `LogId` int(11) NOT NULL AUTO_INCREMENT,
  `DATE` datetime NOT NULL,
  `APP_NAME` varchar(256) NOT NULL,
  `USER_NAME` varchar(256) NOT NULL,
  `TASK` char(3) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `XemlId` char(36) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `StartDate` datetime NOT NULL,
  `ObservationTime` float NOT NULL,
  `Description` varchar(8000) DEFAULT NULL,
  `ExperimenterEmail` varchar(255) DEFAULT NULL,
  `ExperimenterFirstName` varchar(255) DEFAULT NULL,
  `ExperimenterLastName` varchar(255) DEFAULT NULL,
  `Organisation` varchar(255) DEFAULT NULL,
  `NameSpace` varchar(255) NOT NULL,
  `XemlVersion` varchar(255) NOT NULL,
  `Xeml` longtext NOT NULL,
  PRIMARY KEY (`LogId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Event`;
CREATE TABLE `Event` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `TimePoint` float NOT NULL,
  `Label` varchar(255) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`TimePoint`),
  CONSTRAINT `FK_Event_Story` FOREIGN KEY (`FK_Document`, `FK_Story`) REFERENCES `Story` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Experimenter`;
CREATE TABLE `Experimenter` (
  `Email` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `LastName` varchar(255) DEFAULT NULL,
  `Organisation` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `ExperimentKeywords`;
CREATE TABLE `ExperimentKeywords` (
  `FK_Document` smallint(6) NOT NULL,
  `Keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`FK_Document`,`Keyword`),
  CONSTRAINT `FK_ExperimentKeywords_Experiment` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Experiments`;
CREATE TABLE `Experiments` (
  `id` char(38) NOT NULL,
  `name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `ExperimentsChromatograms`;
CREATE TABLE `ExperimentsChromatograms` (
  `experiment_id` char(38) NOT NULL,
  `chromatogram_id` char(38) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Individual`;
CREATE TABLE `Individual` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` int(11) NOT NULL,
  `FK_IndividualsPool` smallint(6) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Individual_IndividualsPool` (`FK_Document`,`FK_IndividualsPool`),
  CONSTRAINT `FK_Individual_IndividualsPool` FOREIGN KEY (`FK_Document`, `FK_IndividualsPool`) REFERENCES `IndividualsPool` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `IndividualsPool`;
CREATE TABLE `IndividualsPool` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `FK_BaseStory` smallint(6) NOT NULL,
  `NS` varchar(255) NOT NULL,
  `GermPlasm` varchar(255) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_IndividualsPool_Document` (`FK_Document`,`FK_BaseStory`),
  CONSTRAINT `FK_IndividualsPool_Document` FOREIGN KEY (`FK_Document`, `FK_BaseStory`) REFERENCES `Story` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Material`;
CREATE TABLE `Material` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `FK_Partition` smallint(6) NOT NULL,
  `FK_Term` smallint(6) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`FK_Partition`,`FK_Term`),
  KEY `FK_Material_Term` (`FK_Document`,`FK_Term`),
  CONSTRAINT `FK_Material_Term` FOREIGN KEY (`FK_Document`, `FK_Term`) REFERENCES `Term` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_Material_Partition` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_Partition`) REFERENCES `Partition` (`FK_Document`, `FK_Story`, `Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Observation`;
CREATE TABLE `Observation` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` int(11) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `FK_ObservationPoint` int(11) NOT NULL,
  `FK_IndividualsPool` smallint(6) NOT NULL,
  `FK_Individual` int(11) DEFAULT NULL,
  `Duration` float NOT NULL,
  `IndividualInfluence` bit(1) NOT NULL DEFAULT b'0',
  `Destructive` bit(1) NOT NULL DEFAULT b'1',
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Observation_Individual` (`FK_Document`,`FK_Individual`),
  KEY `FK_Observation_IndividualsPool` (`FK_Document`,`FK_IndividualsPool`),
  KEY `FK_Observation_ObservationPoint` (`FK_Document`,`FK_Story`,`FK_ObservationPoint`),
  CONSTRAINT `FK_Observation_ObservationPoint` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_ObservationPoint`) REFERENCES `ObservationPoint` (`FK_Document`, `FK_Story`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_Observation_Individual` FOREIGN KEY (`FK_Document`, `FK_Individual`) REFERENCES `Individual` (`FK_Document`, `Id`),
  CONSTRAINT `FK_Observation_IndividualsPool` FOREIGN KEY (`FK_Document`, `FK_IndividualsPool`) REFERENCES `IndividualsPool` (`FK_Document`, `Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `ObservationPoint`;
CREATE TABLE `ObservationPoint` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `Id` int(11) NOT NULL,
  `TargetTime` float NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`Id`),
  CONSTRAINT `FK_ObservationPoint_Story` FOREIGN KEY (`FK_Document`, `FK_Story`) REFERENCES `Story` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Ontology`;
CREATE TABLE `Ontology` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` tinyint(4) NOT NULL,
  `FK_OntologyHandler` tinyint(4) NOT NULL,
  `NS` varchar(255) DEFAULT NULL,
  `NameSpaceAlias` varchar(255) NOT NULL,
  `InstanceLocation` varchar(255) NOT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Ontology_OntologyHandler` (`FK_OntologyHandler`),
  CONSTRAINT `FK_Ontology_OntologyHandler` FOREIGN KEY (`FK_OntologyHandler`) REFERENCES `OntologyHandler` (`Id`),
  CONSTRAINT `FK_NameSpace_Document` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `OntologyHandler`;
CREATE TABLE `OntologyHandler` (
  `Id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `Uri` varchar(255) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Partition`;
CREATE TABLE `Partition` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `FK_Sample` int(11) NOT NULL,
  `FK_Observation` int(11) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`Id`),
  KEY `FK_Partition_Observation` (`FK_Document`,`FK_Observation`),
  KEY `FK_Partition_Sample` (`FK_Document`,`FK_Story`,`FK_Sample`),
  CONSTRAINT `FK_Partition_Sample` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_Sample`) REFERENCES `Sample` (`FK_Document`, `FK_Story`, `Id`),
  CONSTRAINT `FK_Partition_Observation` FOREIGN KEY (`FK_Document`, `FK_Observation`) REFERENCES `Observation` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `ReplicaGroup`;
CREATE TABLE `ReplicaGroup` (
  `FK_Document` smallint(6) NOT NULL,
  `HashCode` int(11) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `Label` varchar(4000) NOT NULL,
  PRIMARY KEY (`FK_Document`,`HashCode`),
  CONSTRAINT `FK_ReplicaGroup_Document` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `ReplicaGroupMapping`;
CREATE TABLE `ReplicaGroupMapping` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `FK_Sample` int(11) NOT NULL,
  `FK_HashCode` int(11) NOT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`FK_Sample`,`FK_HashCode`),
  CONSTRAINT `FK_ReplicaGroupMapping_Sample` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_Sample`) REFERENCES `Sample` (`FK_Document`, `FK_Story`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Sample`;
CREATE TABLE `Sample` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `Id` int(11) NOT NULL,
  `TimeIntermix` varchar(255) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`Id`),
  CONSTRAINT `FK_Sample_Story` FOREIGN KEY (`FK_Document`, `FK_Story`) REFERENCES `Story` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `SampleMapping`;
CREATE TABLE `SampleMapping` (
  `FK_Document` smallint(6) NOT NULL,
  `FK_Story` smallint(6) NOT NULL,
  `FK_Sample` int(11) NOT NULL,
  `FK_DataProvider` tinyint(4) NOT NULL,
  `SubKey` varchar(255) NOT NULL,
  `Value` varchar(255) NOT NULL,
  PRIMARY KEY (`FK_Document`,`FK_Story`,`FK_Sample`,`FK_DataProvider`,`SubKey`),
  KEY `FK_SampleMapping_DataProvider` (`FK_Document`,`FK_DataProvider`),
  CONSTRAINT `FK_SampleMapping_Sample` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_Sample`) REFERENCES `Sample` (`FK_Document`, `FK_Story`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_SampleMapping_DataProvider` FOREIGN KEY (`FK_Document`, `FK_DataProvider`) REFERENCES `DataProvider` (`FK_Document`, `Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Story`;
CREATE TABLE `Story` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `FK_ParentStoryId` smallint(6) DEFAULT NULL,
  `Label` varchar(255) DEFAULT NULL,
  `TimePoint` float DEFAULT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Story_Story` (`FK_Document`,`FK_ParentStoryId`),
  CONSTRAINT `FK_Story_Story` FOREIGN KEY (`FK_Document`, `FK_ParentStoryId`) REFERENCES `Story` (`FK_Document`, `Id`),
  CONSTRAINT `FK_Story_Experiment` FOREIGN KEY (`FK_Document`) REFERENCES `Document` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Term`;
CREATE TABLE `Term` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `FK_Ontology` tinyint(4) NOT NULL,
  `TermId` varchar(255) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `AnnotationId` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Term_Ontology` (`FK_Document`,`FK_Ontology`),
  CONSTRAINT `FK_Term_Ontology` FOREIGN KEY (`FK_Document`, `FK_Ontology`) REFERENCES `Ontology` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `Value`;
CREATE TABLE `Value` (
  `FK_Document` smallint(6) NOT NULL,
  `Id` smallint(6) NOT NULL,
  `FK_Term` smallint(6) NOT NULL,
  `FK_Story` smallint(6) DEFAULT NULL,
  `FK_Partition` smallint(6) DEFAULT NULL,
  `Time` float DEFAULT NULL,
  `CycleId` smallint(6) DEFAULT NULL,
  `Context` varchar(255) NOT NULL,
  `Value` varchar(255) NOT NULL,
  `Unit` varchar(255) DEFAULT NULL,
  `Label` varchar(255) DEFAULT NULL,
  `Duration` float DEFAULT NULL,
  PRIMARY KEY (`FK_Document`,`Id`),
  KEY `FK_Value_Partition` (`FK_Document`,`FK_Story`,`FK_Partition`),
  KEY `FK_Value_Term` (`FK_Document`,`FK_Term`),
  CONSTRAINT `FK_Value_Term` FOREIGN KEY (`FK_Document`, `FK_Term`) REFERENCES `Term` (`FK_Document`, `Id`),
  CONSTRAINT `FK_Value_Partition` FOREIGN KEY (`FK_Document`, `FK_Story`, `FK_Partition`) REFERENCES `Partition` (`FK_Document`, `FK_Story`, `Id`),
  CONSTRAINT `FK_Value_Story` FOREIGN KEY (`FK_Document`, `FK_Story`) REFERENCES `Story` (`FK_Document`, `Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2014-03-06 13:32:35
